﻿/*
============================================================================
文件名    ： DBProvider.cs
完全限定名： SSS.DAL.DBProvider
作者      ： 陈存腾
创建时间  ： 2012-03-11
修改时间  ： 2012-05-07
版本      ：
说明      ： 数据库辅助类－基于ADO.NET对MySQL进行基础封装
============================================================================
*/

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;


namespace SSS.DAL
{
    /// <summary>
    /// 数据库辅助类
    /// </summary>
    internal class DBProvider
    {
        #region 私有字段

        /// <summary>
        /// MySQL数据库连接
        /// </summary>
        private static MySqlConnection sqlCon = null;

        /// <summary>
        /// MySQL数据库CMD
        /// </summary>
        private static MySqlCommand sqlCmd = null;

        /// <summary>
        /// MySQL数据库连接字符串
        /// </summary>
        private static string connectionString = null;

        /// <summary>
        /// 错误信息
        /// 正确执行的话，errorState值为null，否则为错误信息
        /// </summary>
        private static String errorState = null;

        /// <summary>
        /// MySQL数据集
        /// </summary>
        private static MySqlDataReader sqlDataReader = null;

        /// <summary>
        /// MySqlCmd中存储过程的参数集合
        /// </summary>
        private static MyParameterCollection parameters = new MyParameterCollection();

        #endregion

        #region 属性

        /// <summary>
        /// 数据库连接
        /// </summary>
        internal static MySqlConnection MyConnection
        {
            get { return DBProvider.sqlCon; }
        }

        /// <summary>
        /// 数据库CMD
        /// </summary>
        internal static MySqlCommand MyCommand
        {
            get { return DBProvider.sqlCmd; }
        }

        /// <summary>
        /// 数据集
        /// </summary>
        public static MySqlDataReader MyDataReader
        {
            get { return DBProvider.sqlDataReader; }
            set { DBProvider.sqlDataReader = value; }
        }

        /// <summary>
        /// MySqlCmd中存储过程的参数集合
        /// </summary>
        internal static MyParameterCollection Parameters
        {
            get { return DBProvider.parameters; }
            set { DBProvider.parameters = value; }
        }

        /// <summary>
        /// 错误信息
        /// 正确执行的话，errorState值为null，否则为错误信息
        /// </summary>
        public static String ErrorState
        {
            get { return DBProvider.errorState; }
        }

        #endregion

        #region 子类型

        /// <summary>
        /// MySqlCmd中存储过程的参数集合
        /// </summary>
        public class MyParameterCollection
        {
            /// <summary>
            /// 重载的索引器 根据参数名查找存储过程参数的值
            /// </summary>
            /// <param name="name">参数名</param>
            /// <returns>目标存储过程参数的值</returns>
            public Object this[string name]
            {
                get
                {
                    // 传入参数(mysql的存储过程参数是以?打头,大多数数据库系统都是以@开头)
                    name = String.Concat("?", name);
                    if (!DBProvider.sqlCmd.Parameters.Contains(name))
                    {
                        DBProvider.sqlCmd.Parameters.Add(new MySqlParameter(name, MySqlDbType.VarChar, 255));
                    }
                    return DBProvider.sqlCmd.Parameters[name].Value;
                }
                set
                {
                    name = String.Concat("?", name);
                    if (!DBProvider.sqlCmd.Parameters.Contains(name))
                    {
                        DBProvider.sqlCmd.Parameters.Add(new MySqlParameter(name, MySqlDbType.VarChar, 255));
                    }
                    DBProvider.sqlCmd.Parameters[name].Value = value;
                }
            }

            /// <summary>
            /// 重载的索引器 根据参数信息查找存储过程参数的值
            /// </summary>
            /// <param name="name">参数名</param>
            /// <param name="type">参数数据类型</param>
            /// <returns>目标存储过程参数的值</returns>
            public Object this[string name, MySqlDbType type]
            {
                get
                {
                    // 传入参数(mysql的存储过程参数是以?打头,大多数数据库系统都是以@开头)
                    name = String.Concat("?", name);
                    if (!DBProvider.sqlCmd.Parameters.Contains(name))
                    {
                        DBProvider.sqlCmd.Parameters.Add(new MySqlParameter(name, type));
                    }
                    return DBProvider.sqlCmd.Parameters[name].Value;
                }
                set
                {
                    name = String.Concat("?", name);
                    if (!DBProvider.sqlCmd.Parameters.Contains(name))
                    {
                        DBProvider.sqlCmd.Parameters.Add(new MySqlParameter(name, type));
                    }
                    DBProvider.sqlCmd.Parameters[name].Value = value;
                }
            }

            /// <summary>
            /// 重载的索引器 根据参数信息查找存储过程参数的值
            /// </summary>
            /// <param name="name">参数名</param>
            /// <param name="type">参数数据类型</param>
            /// <param name="size">参数大小</param>
            /// <returns>目标存储过程参数的值</returns>
            public Object this[string name, MySqlDbType type, int size]
            {
                get
                {
                    // 传入参数(mysql的存储过程参数是以?打头,大多数数据库系统都是以@开头)
                    name = String.Concat("?", name);
                    if (!DBProvider.sqlCmd.Parameters.Contains(name))
                    {
                        DBProvider.sqlCmd.Parameters.Add(new MySqlParameter(name, type, size));
                    }
                    return DBProvider.sqlCmd.Parameters[name].Value;
                }
                set
                {
                    name = String.Concat("?", name);
                    if (!DBProvider.sqlCmd.Parameters.Contains(name))
                    {
                        DBProvider.sqlCmd.Parameters.Add(new MySqlParameter(name, type, size));
                    }
                    DBProvider.sqlCmd.Parameters[name].Value = value;
                }
            }

            /// <summary>
            /// 重载的索引器 根据参数信息查找存储过程参数的值
            /// </summary>
            /// <param name="name">参数名</param>
            /// <param name="type">参数数据类型</param>
            /// <param name="size">参数大小</param>
            /// <param name="direction">参数访问类型</param>
            /// <returns>目标存储过程参数的值</returns>
            public Object this[string name, MySqlDbType type, int size, ParameterDirection direction]
            {
                get
                {
                    // 传入参数(mysql的存储过程参数是以?打头,大多数数据库系统都是以@开头)
                    name = String.Concat("?", name);
                    if (!DBProvider.sqlCmd.Parameters.Contains(name))
                    {
                        DBProvider.sqlCmd.Parameters.Add(new MySqlParameter(name, type, size));
                        DBProvider.sqlCmd.Parameters[name].Direction = direction;
                    }
                    return DBProvider.sqlCmd.Parameters[name].Value;
                }
                set
                {
                    name = String.Concat("?", name);
                    if (!DBProvider.sqlCmd.Parameters.Contains(name))
                    {
                        DBProvider.sqlCmd.Parameters.Add(new MySqlParameter(name, type, size));
                        DBProvider.sqlCmd.Parameters[name].Direction = direction;
                    }
                    DBProvider.sqlCmd.Parameters[name].Value = value;
                }
            }
        }

        #endregion

        #region 构造函数

        static DBProvider()
        {
            // 从网站Web.config中提取数据库连接字符串
            connectionString = SSS.Config.Configuration.ConnectionString;
            // 新建MySql数据库连接
            connect();
        }

        #endregion

        #region 私有方法

        #region 新建MySql数据库连接

        /// <summary>
        /// 新建MySql数据库连接
        /// </summary>
        /// <returns>操作是否成功</returns>
        private static bool connect()
        {
            try
            {
                // 新建MySql数据库连接及CMD对象
                sqlCon = new MySqlConnection(connectionString);
                sqlCmd = sqlCon.CreateCommand();
                // 设置调用方式为存储过程
                sqlCmd.CommandType = CommandType.StoredProcedure;
                return true;
            }
            catch (System.Exception ex)
            {
                errorState = ex.Message;
                return false;
            }
        }

        #endregion

        #endregion

        #region 公共方法

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns>是否成功打开数据库连接</returns>
        public static bool Open()
        {
            if ((sqlCon.State == ConnectionState.Broken) || (sqlCon.State == ConnectionState.Closed))
            {
                sqlCon.Open();
                return true;
            }
            return false;
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="commandText">执行的SQL命令</param>
        /// <returns>是否成功打开数据库连接</returns>
        public static bool Open(string commandText)
        {
            if ((sqlCon.State == ConnectionState.Broken) || (sqlCon.State == ConnectionState.Closed))
            {
                sqlCmd.CommandText = commandText;
                sqlCon.Open();
                return true;
            }
            return false;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public static void Close()
        {
            if (sqlCon.State != ConnectionState.Closed)
            {
                sqlCon.Close();
            }
        }

        #region 执行

        /// <summary>
        /// 执行增删改等存储过程
        /// </summary>
        /// <returns>记录变更的行数</returns>
        internal static int ExecuteNonQuery()
        {
            try
            {
                return sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行增删改等存储过程
        /// </summary>
        /// <param name="sql">待执行的sql命令</param>
        /// <returns>记录变更的行数</returns>
        internal static int ExecuteNonQuery(string sql)
        {
            sqlCmd.CommandText = sql;
            return ExecuteNonQuery();
        }

        /// <summary>
        /// 执行查询，并返回结果集中第一行第一列
        /// </summary>
        /// <returns>结果集中第一行第一列</returns>
        internal static object ExecuteScalar()
        {
            try
            {
                return sqlCmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行查询，并返回结果集中第一行第一列
        /// </summary>
        /// <param name="sql">待执行的sql命令</param>
        /// <returns>结果集中第一行第一列</returns>
        internal static object ExecuteScalar(string sql)
        {
            sqlCmd.CommandText = sql;
            return ExecuteScalar();
        }

        /// <summary>
        /// 执行查询SQL语句或存储过程
        /// </summary>
        /// <returns>查询结构</returns>
        internal static DataTable ExecuteReader()
        {
            DataTable dataTable = new DataTable();
            using (sqlDataReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dataTable.Load(sqlDataReader);
            }
            return dataTable;
        }

        /// <summary>
        /// 执行查询SQL语句或存储过程
        /// </summary>
        /// <param name="sql">待执行的sql命令</param>
        /// <returns>查询结构</returns>
        internal static DataTable ExecuteReader(string sql)
        {
            sqlCmd.CommandText = sql;
            return ExecuteReader();
        }

        #endregion

        public static System.Data.DataTable test()
        {

            MySql.Data.MySqlClient.MySqlDataReader sdr = null;
            System.Data.DataTable dt = new System.Data.DataTable();
            try
            {
                if (!sqlCmd.Parameters.Contains("?p_no"))
                {
                    sqlCmd.Parameters.Add(new MySqlParameter("?p_no", MySqlDbType.Int32));
                }
                sqlCmd.Parameters["?p_no"].Value = 1;


                sqlCmd.CommandText = "test";
                sqlCon.Open();
                using (sdr = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    dt.Load(sdr);
                }
            }
            catch (System.Exception ex)
            {
                errorState = ex.Message;
            }
            sqlCmd.Parameters.Add(new MySqlParameter("?p_no", MySqlDbType.Int32));
            return dt;
        }

        #endregion
    }
}